package com.weilx959.spt.plugin.e2sp.utils;

import com.weilx959.spt.core.utils.Try;
import com.weilx959.spt.plugin.e2sp.constant.ExcelToSqlConstants;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;

import java.sql.Timestamp;
import java.text.NumberFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Calendar;
import java.util.Date;
import java.util.Objects;

/**
 * Excel工具类
 *
 * @author weilx
 * @since 2024-04-30
 */
@Slf4j
public class ExcelUtil {
    private ExcelUtil() {}

    private static final NumberFormat NUMBER_FORMAT = NumberFormat.getInstance();

    static {
        NUMBER_FORMAT.setGroupingUsed(false);
        NUMBER_FORMAT.setParseIntegerOnly(false);
    }

    /**
     * 支持多种类型值读取
     *
     * @param cell 单元格
     * @param formulaEvaluator 公式计算器
     * @return 单元格值
     */
    public static Object getCellValue(Cell cell, FormulaEvaluator formulaEvaluator) {
        return Try.of(() -> formulaEvaluator.evaluate(cell))
            .map(cellValue -> {
                if (cellValue == null) {
                    return StringUtils.EMPTY;
                }
                switch (cellValue.getCellType()) {
                    case STRING -> {
                        return cellValue.getStringValue();
                    }
                    case NUMERIC -> {
                        if (DateUtil.isCellDateFormatted(cell)) {
                            // 格式化日期：yyyy-MM-dd HH:mm:ss
                            return new Timestamp(cell.getDateCellValue().getTime());
                        } else {
                            // 格式化数字
                            return NUMBER_FORMAT.format(cellValue.getNumberValue());
                        }
                    }
                    case BOOLEAN -> {
                        return cellValue.getBooleanValue();
                    }
                    case ERROR -> {
                        return "$ERROR";
                    }
                    default -> { // BLANK or OTHER
                        return StringUtils.EMPTY;
                    }
                }
            })
            .failure(e -> log.error("Read cell value error：{}", e.getMessage()))
            .orElse("$EVALUATE_ERROR");
    }

    /**
     * 设置单元格值
     *
     * @param cell  单间格
     * @param value 值
     */
    public static void setCellValue(Cell cell, Object value) {
        if (Objects.isNull(cell) || Objects.isNull(value)) {
            return;
        }
        if (value instanceof Number number) {
            cell.setCellValue(number.doubleValue());
        } else if (value instanceof Date date) {
            cell.setCellValue(date);
        } else if (value instanceof LocalDateTime localDateTime) {
            cell.setCellValue(localDateTime);
        } else if (value instanceof LocalDate localDate) {
            cell.setCellValue(localDate);
        } else if (value instanceof Calendar calendar) {
            cell.setCellValue(calendar);
        } else if (value instanceof Boolean booleanValue) {
            cell.setCellValue(booleanValue);
        } else {
            cell.setCellValue(String.valueOf(value));
        }
    }

    public static String getSchemaName(String sheetName) {
        if (sheetName.contains(ExcelToSqlConstants.DOT)) {
            return StringUtils.split(sheetName, ExcelToSqlConstants.DOT)[0];
        }
        return null;
    }

    public static String getTableName(String sheetName) {
        if (sheetName.contains(ExcelToSqlConstants.DOT)) {
            return StringUtils.split(sheetName, ExcelToSqlConstants.DOT)[1];
        }
        return sheetName;
    }

    /**
     * 获取最后一列编号
     *
     * @param sheet            sheet
     * @param formulaEvaluator 公式计算器
     * @param index            起始行索引
     * @return 列数
     */
    public static int getLastColumnNum(Sheet sheet, FormulaEvaluator formulaEvaluator, int index) {
        if (Objects.isNull(sheet)) {
            return 0;
        }
        Row row = sheet.getRow(index);

        if (Objects.isNull(row)) {
            return 0;
        }
        for (int i = row.getLastCellNum(); i > 0; i--) {
            Object cellValue = ExcelUtil.getCellValue(row.getCell(i - 1), formulaEvaluator);

            if (Objects.nonNull(cellValue) && StringUtils.isNotBlank(String.valueOf(cellValue))) {
                return i;
            }
        }
        return 0;
    }

    /**
     * 获取最大列数
     *
     * @param sheet sheet
     * @param formulaEvaluator 公式计算器
     * @return 最大列数
     */
    public static int getMaxColumnNum(Sheet sheet, FormulaEvaluator formulaEvaluator) {
        if (Objects.isNull(sheet)) {
            return 0;
        }
        int lastRowNum = sheet.getLastRowNum();
        int maxColumnNum = 0;

        for (int i = lastRowNum; i >= 0; i--) {
            maxColumnNum = Math.max(getLastColumnNum(sheet, formulaEvaluator, i), maxColumnNum);
        }
        return maxColumnNum;
    }
}
